/*
* sop管理管理模块
*/
const express = require('express');
const router = express.Router();
// 引入数据库
const db = require("../../config/db");
// 引入时间包
const moment = require("moment");
// 引入方法库
const { toCamelCase, toUnderlineCase, verifyToken, recursionGenerateTree} = require("../../config/utils");
// 引入日志
const log = require("../log");

// 终端，获取所有sop管理列表
router.post("/list", async (req, res) => {
    console.log("===========终端，获取sop管理列表接口============");
    // 获取参数
    let { deptName, status } = req.body;
    // 拼接sql语句
    let sql = "select * from sop_manage";
    let sql_where = ` where 1=1`;
   
    if(deptName){
        sql_where += ` and ancestors = '0,100' and dept_name like '%${deptName}%' or dept_num like '%${deptName}%'`;
    }
    if( deptName || status ){
        sql += sql_where;
    }
   
    // 查询数据库
    db.query("查询sop管理列表", sql, (err, result)=>{
        if(err){
            res.send({code: 500, msg: "终端，查询sop管理列表失败", data: err});
            return;
        }
        if(result.length > 0){
            // 变量Json数组key转成下划线第一个大写
            result = result.map(item => {
               return toCamelCase(item)
           })
           // 根据父类id查询子类
           const parentId = result[0].deptId;
           const sql_child = `select * from sop_manage where ancestors = '0,100,${parentId}'`;
          
           db.query("终端，查询sop管理==根据父类id查询子类", sql_child, (err, sql_child_result)=>{
               if(err){
                   res.send({code: 500, msg: "终端，查询sop管理==根据父类id查询子类失败", data: err});
                   return;
               }
               res.send({code: 200, msg: "终端，查询sop管理==根据父类id查询子类成功", data: result, child: sql_child_result });
           })
       }else{
           res.send({code: 200, msg: "终端，查询sop管理==根据父类id查询子类成功", data: [], total: 0});
       }
    })
})
// 终端，获取工序信息
router.post("/listChild", async (req, res) => {
    console.log("===========终端，获取工序信息============");
    // 获取参数
    let { device } = req.body;
    // 根据设备id查询工序
    let sql = `select * from sop_manage where ancestors like '%${device}%'`;
   
    // 查询数据库
    db.query("查询终端，获取工序信息", sql, (err, result)=>{
        if(err){
            res.send({code: 500, msg: "查询终端，获取工序信息失败", data: err});
            return;
        }
        if(result.length > 0){
            // 变量Json数组key转成下划线第一个大写
            result = result.map(item => {
               return toCamelCase(item)
           })
           res.send({code: 200, msg: "查询终端，获取工序信息==根据设备id查询子类成功", data: result });
       }else{
           res.send({code: 200, msg: "查询终端，获取工序信息==根据设备id查询子类成功", data: [] });
       }
    })
})

// 获取所有sop管理列表
router.get("/list", async (req, res) => {
    console.log("===========获取sop管理列表接口============");
    // 获取参数
    let { deptName, status } = req.query;
    // 拼接sql语句
    let sql = "select * from sop_manage";
    let sql_where = ` where 1=1`;
   
    if(deptName){
        sql_where += ` and dept_id = '${deptName}' or ancestors like '%${deptName}%'`;
    }else {
        return res.send({code: 200, msg: "查询sop管理列表成功", data: [], total: 0});
    }
    if(status){
        sql_where += ` and status = '${status}'`;
    }
    if( deptName || status ){
        sql += sql_where;
    }

    // 查询数据库
    db.query("查询sop管理列表", sql, (err, result)=>{
        if(err){
            res.send({code: 500, msg: "查询sop管理列表失败", data: err});
            return;
        }
        if(result.length > 0){
            // 变量Json数组key转成下划线第一个大写
            result = result.map(item => {
               return toCamelCase(item)
           })
           res.send({code: 200, msg: "查询sop管理列表成功", data: result });
       }else{
           res.send({code: 200, msg: "查询sop管理列表成功", data: [], total: 0});
       }
    })
})

// 新增sop管理
router.post("/", (req, res)=>{
    console.log("===========新增sop管理接口============");
    // 获取参数
    let {parentId, deptName, orderNum, status, leader, phone, email, deptNum, tableData} = req.body;
    // 获取创建人
    let {username} = verifyToken(req.headers.authorization);
    // 获取创建时间
    let createTime = moment().format("YYYY-MM-DD HH:mm:ss");
    // 判断当前层级查询是否重复顺序和重复名称
    let sql_check = `select * from sop_manage where parent_id = '${parentId}' and (order_num = '${orderNum}' or dept_name = '${deptName}')`;
    db.query("判断当前层级查询是否重复顺序和重复名称", sql_check, (err, sql_check_result)=>{
        if(err){
            res.send({code: 500, msg: "判断当前层级查询是否重复顺序和重复名称失败", data: err});
            return;
        }
        if(sql_check_result.length > 0){
            res.send({code: 500, msg: "当前层级，已存在相同顺序或名称，请重新输入！"});
            return;
        }
        // 获取父级
        db.query("查询父级", `select ancestors from sop_manage where dept_id = ${parentId}`, (err, sql_parent_result)=>{
            if(err){
                res.send({code: 500, msg: "查询父级失败", data: err});
                return;
            }
           // 父类层级
           console.log(sql_parent_result[0].ancestors);
           if(sql_parent_result.length > 0){
                let ancestors = sql_parent_result[0].ancestors + ',' + parentId;
                // 插入数据库
                let sql = `insert into sop_manage (dept_name, dept_num, parent_id, order_num, status, leader, phone, email, table_data, create_by, create_time, ancestors)
                values 
                ('${deptName}', ${deptNum ? "'"+deptNum+"'" : null}, '${parentId}','${orderNum}', '${status}', ${leader ? "'"+leader+"'" : null}, 
                ${phone ? "'"+phone+"'" : null}, ${email ? "'"+email+"'" : null}, ${tableData ? "'"+tableData+"'" : null},
                '${username}', '${createTime}', '${ancestors}')`;
                db.query("新增sop管理", sql, (err, result)=>{
                    if(err){
                        res.send({code: 500, msg: "新增sop管理失败", data: err});
                        return;
                    }
                    if(result.affectedRows > 0){
                        res.send({code: 200, msg: "新增sop管理成功"});
                    }else{
                        res.send({code: 500, msg: "新增sop管理失败"});
                    }
                })
           }
           
        })
        
    })
})

// 查询单个sop管理
router.get(/^\/(\d+)$/, (req, res) => {
    console.log("===========查询单个sop管理接口，正则匹配============");
    // 获取参数
    let url_arr = req.url.split("/");
    const id = url_arr[url_arr.length - 1];
   
    db.query("查询单个sop管理", `select * from sop_manage where dept_id = ${id}`, (err, result)=>{
        if(err){
            res.send({code: 500, msg: "查询单个sop管理失败", data: err});
            return;
        }
        if(result.length > 0){
            // 变量Json数组key转成下划线第一个大写
            result = result.map(item => {
                return toCamelCase(item)
            })
            res.send({code: 200, msg: "查询单个sop管理成功", data: result[0]});
        }else{
            res.send({code: 500, msg: "查询单个sop管理失败"});
        }
    })
})

// 查询当前层级所有信息sop管理列表
router.get("/list/exclude/:id", (req, res) => {
    console.log("===========查询当前层级所有信息sop管理列表接口============");
    // 获取参数
    const id = req.params.id;
   
    db.query("查询当前层级所有信息sop管理列表", `select * from sop_manage where dept_id = ${id}`, (err, result)=>{
        if(err){
            res.send({code: 500, msg: "查询当前层级所有信息sop管理列表失败", data: err});
            return;
        }
        // 根据ancestors字段查询所有父级
        let sql_parent = `select * from sop_manage where dept_id in (${result[0].ancestors})`
        db.query("根据ancestors字段查询所有父级", sql_parent, (err, result_parent)=>{
            if(err){
                res.send({code: 500, msg: "根据ancestors字段查询所有父级失败", data: err});
                return;
            }
            // 合并父级和当前层级
            result = [...result_parent, ...result];
            if(result.length > 0){
                // 变量Json数组key转成下划线第一个大写
                result = result.map(item => {
                    return toCamelCase(item)
                })
                res.send({code: 200, msg: "查询当前层级所有信息sop管理列表成功", data: result});
            }else{
                res.send({code: 500, msg: "查询当前层级所有信息sop管理列表失败"});
            }
        })
    })
})

// 修改sop管理
router.put("/", (req, res)=>{
    console.log("===========修改sop管理接口============");
    // 获取参数
    let {parentId, deptName, orderNum, status, leader, phone, email, deptId, deptNum, tableData} = req.body;
    // 获取修改人
    let {username} = verifyToken(req.headers.authorization);
    // 获取修改时间
    let updateTime = moment().format("YYYY-MM-DD HH:mm:ss");
    // 判断当前层级查询是否重复顺序和重复名称
    let sql_check = `select * from sop_manage where parent_id = '${parentId}' and (order_num = '${orderNum}' or dept_name = '${deptName}') and dept_id != '${deptId}'`;
    db.query("判断当前层级查询是否重复顺序和重复名称", sql_check, (err, sql_check_result)=>{
        if(err){
            res.send({code: 500, msg: "判断当前层级查询是否重复顺序和重复名称失败", data: err});
            return;
        }
        if(sql_check_result.length > 0){
            res.send({code: 500, msg: "当前层级，已存在相同顺序或名称，请重新输入！"});
            return;
        }
        // 修改数据库
        let sql = `update sop_manage set parent_id = '${parentId}',  dept_name = '${deptName}', dept_num = ${deptNum ? "'"+deptNum+"'" : null}, order_num = '${orderNum}', status = '${status}',
        leader = ${leader ? "'"+leader+"'" : null}, phone = ${phone ? "'"+phone+"'" : null},  email = ${email ? "'"+email+"'" : null},  table_data = ${tableData ? "'"+tableData+"'" : null}, 
        update_by = '${username}', update_time = '${updateTime}'
        where dept_id = ${deptId}`;
        db.query("修改sop管理", sql, (err, result)=>{
            if(err){
                res.send({code: 500, msg: "修改sop管理失败", data: err});
                return;
            }
            if(result.affectedRows > 0){
                res.send({code: 200, msg: "修改sop管理成功"});
            }else{
                res.send({code: 500, msg: "修改sop管理失败"});
            }
        })
    })
})

// 删除sop管理
router.delete("/:id", (req, res) => {
    console.log("===========删除sop管理接口============");
    // 获取参数
    let {id} = req.params;
     // 拼接sql,删除多条数据
     let sql = `delete from sop_manage where dept_id in(${id})`;
    // 查询子分类是否存在
    db.query("查询子分类是否存在", `select * from sop_manage where  ancestors like '%${id}%'`, (err, result)=>{
        if(err){
            res.send({code: 500, msg: "查询子分类是否存在失败", data: err});
            return;
        }
        if(result.length > 0){
            res.send({code: 500, msg: '存在子分类，无法删除，请先删除子分类'})
            return
        }
        db.query("删除sop管理", sql, (err, result) => {
            if (err) {
                res.send({code: 500, msg: "删除sop管理失败", data: err});
                return
            }
            if(result.affectedRows > 0){
                res.send({code: 200, msg: "删除sop管理成功"})
            }else{
                res.send({code: 500, msg: "删除sop管理失败"})
            }
        })
    })
})

// 查询sop管理下拉树结构接口
router.get('/treeselect', (req, res) => {
    console.log("===========查询sop管理下拉树结构接口============");
    // 查询数据库
    db.query("查询sop管理下拉树结构接口", `select dept_id as id, dept_name as label, parent_id as pid  from sop_manage`, (err, result) => {
        if (err) { 
            res.send({code: 500, msg: '查询sop管理下拉树结构接口失败'}) 
            return
        }
        if(result.length > 0) {
            // 生成树结构递归
            result = recursionGenerateTree(0, result)
        }
        res.send({
            code: 200,
            msg: '测试数据',
            data: result,
        })
    })
   
})

// 查询角色下对应sop管理下拉树结构接口
router.get('/roleDeptTreeselect/:id', (req, res) => {
    console.log("===========查询sop管理下拉树结构接口============");
    // 获取参数
    let {id} = req.params;
    // 连表查询 根据角色id查询sop管理id，再查询sop管理信息
    let sql_union = `select dept_id as id, dept_name as label, parent_id as pid  from sop_manage 
    where dept_id in (select dept_id from sys_role_dept where role_id = ${id})`;

    // 查询数据库
    db.query("查询sop管理下拉树结构接口", sql_union, (err, result) => {
        if (err) { 
            res.send({code: 500, msg: '查询sop管理下拉树结构接口失败'}) 
            return
        }
        if(result.length > 0) {
            // 生成树结构递归
            result = recursionGenerateTree(0, result)
        }
        res.send({
            code: 200,
            msg: '角色下对应sop管理下拉树结构成功',
            depts: result,
        })
    })
   
})

// 查询sop管理列表总数接口
router.get('/total', (req, res) => {
    console.log("===========查询sop管理列表总数接口============");
    // 查询数据库、项目总数、设备总数、工序总数、停用总数
    const sql = `SELECT count(*) as projectTotal FROM sop_manage WHERE ancestors LIKE "0,100" 
                UNION all
                SELECT count(*) as deviceTotal1 FROM sop_manage WHERE ancestors LIKE "0,100,___" 
                UNION all
                SELECT count(*) as proTotal1 FROM sop_manage WHERE ancestors LIKE "0,100,___,%"
                UNION all
                SELECT count(*) as statueSotal FROM sop_manage   WHERE status = '1'`;
    db.query("查询sop管理列表总数接口", sql, (err, result) => {
        if (err) {
            res.send({code: 500, msg: '查询sop管理列表总数接口失败'})
            return
        }
        // 查询项目总数
        res.send({
            code: 200,
            msg: '查询sop管理列表总数接口成功',
            projectTotal: result[0].projectTotal,
            deviceTotal: result[1].projectTotal,
            proTotal: result[2].projectTotal,
            statueSotal: result[3].projectTotal,
        })
    })
})

// 查询soop工序列表接口
router.get('/proList', (req, res) => {
    console.log("===========查询soop工序列表接口============");
    // 查询最新创建的10条数据库
    const sql = `select * from sop_manage where ancestors LIKE "0,100,___,%" order by create_time asc limit 10`;
    db.query("查询soop工序列表接口", sql, (err, result) => {
        if (err) {
            res.send({code: 500, msg: '查询soop工序列表接口失败'})
            return
        }
        if(result.length > 0){
            // 变量Json数组key转成下划线第一个大写
            result = result.map(item => {
                return toCamelCase(item)
            })
            res.send({code: 200, msg: "查询soop工序列表接口成功", data: result});
        }else{
            res.send({code: 500, msg: "查询soop工序列表接口失败"});
        }
    })
})

// 更新sop管理接口两个id更新不一样值
router.post('/update', (req, res) => {
    console.log("===========更新sop管理接口两个id更新不一样值============");
    const {oldDeptId, newDeptId, newOrderNum, oldOrderNum} = req.body;
    // 更新两个id，更新排序值
    const sql = `UPDATE sop_manage 
                set order_num = case
                when dept_id = ${oldDeptId} then '${oldOrderNum}'
                when dept_id = ${newDeptId} then '${newOrderNum}'
                else order_num end
                where dept_id in (${oldDeptId},${newDeptId})
                `
    // 更新数据库
    console.log(sql);
    db.query("更新sop管理接口两个id更新不一样值", sql, (err, result) => {
        if (err) {
            res.send({code: 500, msg: '更新sop管理接口两个id更新不一样值失败', data: err})
            return
        }
        res.send({code: 200, msg: '更新sop管理接口两个id更新不一样值成功'})
    })
})

// 查询sop管理项目总数接口
router.get('/totalProject', (req, res) => {
    console.log("===========查询sop管理项目总数接口============");
    const sql = `SELECT count(*) as projectTotal FROM sop_manage WHERE ancestors LIKE "0,100"`;
    db.query("查询sop管理项目总数接口", sql, (err, result) => {
        if (err) {
            res.send({code: 500, msg: '查询sop管理项目总数接口失败'})
            return
        }
        // 查询项目总数
        res.send({
            code: 200,
            msg: '查询sop管理项目总数接口成功',
            projectTotal: result[0].projectTotal,
        })
    })
})

// 模糊查询组件和工序信息，复用组件内容（无法独立复用）当一张表
router.post('/selectDateNo', (req, res) => {
    console.log("===========模糊查询组件和工序信息，复用组件内容============");
    const {ancestors, name} = req.body;
    const sql = `select * from sop_manage where ancestors LIKE "${ancestors}" and dept_name like'%${name}%'`;
    db.query("模糊查询组件和工序信息，复用组件内容", sql, (err, result) => {
        if (err) {
            res.send({code: 500, msg: '模糊查询组件和工序信息，复用组件内容失败'})
            return
        }
        // 变量Json数组key转成下划线第一个大写
        result = result.map(item => {
            return toCamelCase(item)
        })
        res.send({
            code: 200,
            msg: '模糊查询组件和工序信息，复用组件内容成功',
            data: result,
        })
    })
})

module.exports = router;
